#! /bin/env python3
# coding: utf-8

'''
author: auqf
func：出入场数据报表统计
time: 2020-11-09 18:28
'''

import os

from common.get_datetime import get_current_date_str
from common.load_cfg import load_cfg
from common.my_excel import write_excel
from common.my_mysql import get_data
from common.exception_alert import alert_admin
from notify_media.send_mail import SendMail

# report1.有出入场数据+有订单
report1 = "select subsystem_code, subsystem_name, sum(park_in_cnt), sum(park_out_cnt), sum(order_cnt) from valid_project_static where check_day between subdate(curdate(), date_format(curdate(), '%w') -1) and subdate(curdate(), date_format(curdate(), '%w') - 7) group by subsystem_code having sum(park_in_cnt) > 0 and sum(park_out_cnt) > 0 and sum(order_cnt) > 0"
# report2.有出入场数据+无订单
report2 = "select subsystem_code, subsystem_name, sum(park_in_cnt), sum(park_out_cnt), sum(order_cnt) from valid_project_static where check_day between subdate(curdate(), date_format(curdate(), '%w') -1) and subdate(curdate(), date_format(curdate(), '%w') - 7) group by subsystem_code having sum(park_in_cnt) > 0 and sum(park_out_cnt) > 0 and sum(order_cnt) = 0"
# report3.无出入场数据+有订单
report3 = "select subsystem_code, subsystem_name, sum(park_in_cnt), sum(park_out_cnt), sum(order_cnt) from valid_project_static where check_day between subdate(curdate(), date_format(curdate(), '%w') -1) and subdate(curdate(), date_format(curdate(), '%w') - 7) group by subsystem_code having sum(park_in_cnt) = 0 and sum(park_out_cnt) = 0 and sum(order_cnt) > 0"
# report4.无出入场数据+无订单
report4 = "select subsystem_code, subsystem_name, sum(park_in_cnt), sum(park_out_cnt), sum(order_cnt) from valid_project_static where check_day between subdate(curdate(), date_format(curdate(), '%w') -1) and subdate(curdate(), date_format(curdate(), '%w') - 7) group by subsystem_code having sum(park_in_cnt) = 0 and sum(park_out_cnt) = 0 and sum(order_cnt) = 0"
reports = [report1, report2, report3, report4]

xls_path = u"/tmp/本周车场情况统计报表-{}.xls".format(get_current_date_str())
file_path, file_name = os.path.split(xls_path)
sheet_name = [u"出入&订单正常", u"出入正常&订单异常", u"出入异常&订单正常", u"出入异常&订单异常"]
tab_header = ['subsystem_code', 'subsystem_name', 'park_in', 'park_out', 'order_num']
ywpt_n_240 = load_cfg('ywpt_n_240')
summary = []
try:
    for sql in reports:
        tab_content = get_data(ywpt_n_240, sql)['res']
        index = reports.index(sql)
        write_excel(xls_path, sheet_name[index], tab_header, tab_content)
        summary.append(len(tab_content))
    summary_content = (summary,)
    write_excel(xls_path, 'summary', sheet_name, summary_content)
    subject = file_name.split('-')[0]
    receiver = ['yuehua.jiang@jieshun.cn']
    sm = SendMail(subject, receiver, file_path)
    sm.send_mail_info()
    os.remove(xls_path)
except:
    alert_admin()
